<?php
namespace app\admin\controller;
use think\Db;
class User extends Base
{
    public $idcard;
    public $bank;
    
    public function __construct(){
        parent::__construct();
        set_time_limit(80);
        @ini_set('memory_limit', '64M');
        $d = new \app\common\model\Data(); 
        $this->idcard = $d->getIdcard(); 
        $this->bank = $d->getBank();
    }
    
	public function lists( $type=0 ){
	    if($type==0){#配置页面
            $d = [
                'i_age',
                'i_sex',
                'i_birthday',
                'b_type',
                'p_yy',
                'froms',
            ];
	        $temp1 = Db::name('ext_field')->where('uuid','not in', $d)->select();
	        $temp2 = Db::name('ext_field')->select();
	        $d = [
	           'nav'    => ['搜索配置', '表单'],
	           'temp1'  => $temp1,
	           'temp2'  => $temp2,
	        ];
	        return view('user/config', $d);
	    }else{#显示页面
            if( is_post() ){
                $pagesize = input('pagesize');
                $page    = input('page');
                $keyword = input('keyword');
                $i_age   = input('i_age');
                $b_type  = input('b_type');
                $p_yy    = input('p_yy');
                $froms   = input('froms');
                
                #sql
                $sql = "
                    select
                        [1]
                    from
                        user
                    where 
                        1 = ?
                    ";
                $param = [1];
                if ( $i_age != '100all' ) {
                    $temp = explode("-", $i_age);
                    $sql .= " and (i_age < ? and i_age > ?)";
                    $param[] = $temp[1];
                    $param[] = $temp[0];
                }
                if ( $b_type != '100all' ) {
                    $sql .= " and b_type=?";
                    $param[] = $b_type;
                }
                if ( $p_yy != '100all' ) {
                    $sql .= " and p_yy=?";
                    $param[] = $p_yy;
                }
                if ( $froms != '100all' ) {
                    $sql .= " and froms = ?";
                    $param[] = $froms;
                }
                if ( !empty($keyword) ) {
                    $sql .= " and ( ";
                    #搜素项
                    $rs = Db::name('ext_field')->where('search_key',0)->column("uuid");
                    foreach($rs as $v){
                        $sql .= " {$v} like ? or";
                        $param[] = "%{$keyword}%";
                    }
                    $sql = rtrim($sql,"or");
                    $sql .= " ) ";
                }
                
                #分页
                $sql_count = str_replace("[1]", "count(*) as total", $sql);
                $total     = Db::query($sql_count, $param);
                $total     = $total[0]['total'];
                $page_size   = $pagesize;
                $page        = com_cur_page($total, $page,  $page_size);
    			$page_data   = com_page_data($total, $page,  $page_size);
    			
    			
                //列表
    			$start       = ($page - 1) * $page_size;
    			$sql        .= " order by id desc limit $start, $page_size";
                
    			#展现字段
                $show_field = Db::name('ext_field')->field("title,uuid")->where('show_field',0)->select();
                $field = "id,";
                $arr_field = ['id'];
                $td_head = "<thead><tr><th width=50>ID</th>";
                foreach($show_field as $val){
                    $arr_field[] = $val['uuid'];
                    $field .= $val['uuid'].",";
                    $td_head .= "<th>{$val['title']}</th>";
                }
                $field = rtrim($field,",");
                $td_head .= "</tr></thead>";
                
                #数据
    			$sql_list    = str_replace("[1]", $field, $sql);
                $list        = Db::query($sql_list, $param);
                
                
                #来源
                $rs = Db::name("froms")->field("id,title")->select();
                $froms = [];
                foreach($rs as $val){
                    $froms[$val['id']] = $val['title'];
                }
                
                //格式数据
                foreach($list as $k => $v){
                    foreach($arr_field as $f){
                        switch ($f){
                            case "create_time":
                                $list[$k]['create_time'] = com_format_time($v['create_time']);
                                break;
                            case "phone_froms":
                                $tmp = explode(",", $v['phone_froms']);
                                $list[$k]['phone_froms'] = "";
                                foreach($tmp as $tmp1){
                                    $list[$k]['phone_froms'] .= $froms[$tmp1].",";
                                }
                                $list[$k]['phone_froms'] = rtrim($list[$k]['phone_froms'],",");
                                break;
                            case "idcard_froms":
                                $tmp = explode(",", $v['idcard_froms']);
                                $list[$k]['idcard_froms'] = "";
                                foreach($tmp as $tmp1){
                                    $list[$k]['idcard_froms'] .= $froms[$tmp1].",";
                                }
                                $list[$k]['idcard_froms'] = rtrim($list[$k]['idcard_froms'],",");
                                break;
                            case "froms":
                                $list[$k]['froms'] = $froms[$v['froms']];
                                break;
                            case "p_yy":
                                $list[$k]['p_yy'] = c("type.p_yy",$v['p_yy']);
                                break;
                            case "i_sex":
                                $list[$k]['i_sex'] = c("type.i_sex",$v['i_sex']);
                                break;    
                            case "i_birthday":
                                $list[$k]['i_birthday'] = com_format_time(abs($v['i_birthday']));
                                break;  
                            case "b_type":
                                $list[$k]['b_type'] = c("type.b_type",$v['b_type']);
                                break;       
                        }   
                    }
                }
    			$com_body    = com_build_table($list, $arr_field);
    			$com_page    = com_build_page( $page_data );
                $config = [
                    'data'   => [
                        "com_body"  => $td_head."<tbody>".$com_body."</tbody>",
                        "com_page"  => $com_page,
                    ],
                    'suc'    => [
                        'js_action' => 'function.iphone',
                    ],
                ];
    			com_check($config);
            }
	        $froms = [];
	        $rs = Db::name('froms')->select();
	        foreach ($rs as $v){
	            $froms[$v['id']] = $v['title'];
	        }
	        $data = [
	            'nav'       => ['数据管理', '列表'],
	            'i_age'     => ['100all'=>'年龄段'] + c('type.i_age'),
	            'b_type'    => ['100all'=>'卡类型'] + c('type.b_type'),
	            'p_yy'      => ['100all'=>'运营商'] + c('type.p_yy'),
	            'froms'     => ['100all'=>'来源']  + $froms,
	        ];
	        return view('', $data);
	    }
    }
    
    //搜搜配置项
    public function configs(){
        if(is_post()){
            $d = input();
            if(!isset($d['search_key'])){
                com_error(['请至少选择一个搜索项']);
                com_check();
            }
            if(!isset($d['show_field'])){
                com_error(['请至少选择一个显示项']);
                com_check();
            }
            Db::query("update ext_field set search_key=1,show_field=1");
            Db::name("ext_field")->where('id','in', $d['search_key'])->update(['search_key'=>0]);
            Db::name("ext_field")->where('id','in', $d['show_field'])->update(['show_field'=>0]);
            com_check(['suc'=>['url'=>url('user/lists','type=1'),'msg'=>'设置成功!']]);
        }
    }
    
    //添加
    public function add(){

        if(is_post()){
            $d = com_data(input());
            $rule = [
                    'froms'         =>  'require',
                    'attach'     =>  'require',
                ];
            $msg = [
                   'froms.require'      =>  '来源不能为空',
                   'attach.require'      =>  'txt不能为空',
                ];
            com_validate( $rule, $msg, $d, true );
            if(strpos($d['attach'],'.txt')===false){
                com_error('请上传txt文件');
                com_check();
            }
            #读取第一行
            $line1 = com_read_file($d['attach'],1);
            $title = explode(",", $line1[0]);

            #字段
            $fields = [];
            foreach($title as $k => $v){
                $v = str_replace(array("\r\n", "\n", "\r"," "),"", $v);
                $rs = Db::name('ext_field')->where('title',$v)->find();
                if(!$rs){
                    com_error(['file'=>'表头错误']);
                    com_check();
                }else{
                    $fields[$rs['uuid']] = $rs['long'];
                }
            }
            if($fields){
                if(!isset($fields['email']) && !isset($fields['idcard']) && !isset($fields['phone'])){
                    com_error(['file'=>'缺少标识!']);   
                    com_check();
                }else{
                    $id = Db::name('task')->insertGetId(['fields'=>json_encode($fields),'froms'=>$d['froms'], 'attach'=>$d['attach'], 'status'=>0,'create_time'=>time()]);
                    $config = [
                        'data' => url('user/up','id='.$id),
                        'suc' => [
                            'msg' => '数据上传成功,等待入库。请不要关闭浏览器',
                            'js_action' => 'alert|function.jump',
                        ],
                    ];
                    com_check( $config );
                }
            }else{
                com_error(['file'=>'没有表头']);
                com_check();
            }
        }
        
        
        #模板
        $rs = Db::name('ext_field')->select();
        $d = [
            'phone_froms',
            'phone_idcards',
            'idcard_froms',
            'idcard_phones',
            'p_pro',
            'p_city',
            'p_county',
            'p_yy',
            'i_pro',
            'i_city',
            'i_county',
            'i_sex',
            'i_age',
            'i_birthday',
            'b_name',
            'b_type',
            'b_title',
            'froms',
        ];
        $temp1 = [];
        $temp2 = []; 
        foreach($rs as $v){
            if(in_array($v['uuid'], $d)){
                $temp1[] = $v;
            }else{
                $temp2[] = $v;
            }   
        }
        #来源下拉
        $froms = [];
        $rs = Db::name('froms')->select();
        foreach ($rs as $v){
            $froms[$v['id']] = $v['title'];
        }
        $d = [
            'nav'       => ['数据上传', '表单'],
            'temp1'     => $temp1,
            'temp2'     => $temp2,
            'froms'     => $froms,
        ];
        return view('', $d);
    }
    
    //更新
    public function up( $id, $page=1 ){
        $rs = Db::name('task')->where('id',$id)->where('status',0)->find();
        if(!$rs){
           exit('无效的任务!');
        }
        
        #锁定
        Db::name('task')->update(['status'=>1,'id'=>$id]);

        #任务
        $file  = $rs['attach'];
        $froms = $rs['froms'];
        $fields= json_decode($rs['fields'],true);
        
        #取得数据
        $time   = time();
        $length = 10000;
        $start  = ($page-1)*$length+1;
        $data   = com_read_file($file, $length, $start);
        $data   = array_filter($data);
        
        #已完全入库
        if(!$data){
            Db::name('task')->update(['status'=>2,'id'=>$id]);
            echo "全部入库完成， 等待跳转归整全部数据库！...";
            exit("<script>window.location.href='".url("user/formate")."';</script>");
        }
        
        #入库
        $temp    = [];
        $history = [];
        $i=0;$j=0;
        foreach( $data as $k => $val ){
            $i++;
            $rs = $this->check($fields, $val);
            if(!$rs){
                continue;
            }else{
                #格式化数据
                $d = $this->getData($rs);
                if($d){
                    $d['tid'] = $id;
                    $d['froms'] = $froms;
                    $d['create_time'] = $time;
                    $j++;
                    #user_tmp表
                    $temp[] = ['content'=>json_encode($rs),'create_time'=>$time, 'froms'=>$froms,'tid'=>$id];
                    #user_history表
                    $history[] = $d;
                }else{
                    continue;
                }
            }
        }
        #task
        Db::query("update task set i=i+?,j=j+?,update_time=? where id=?",[$i,$j, $time,$id]);
        #user_tmp
        Db::name('user_tmp')->insertAll($temp);
        
        #user_history
        Db::name('user_history')->insertAll($history);
        Db::name('user')->insertAll($history);
        Db::name('task')->update(['status'=>0,'id'=>$id]);
        $p = $page+1;
        echo "第{$page}页入库完成， 等待跳转下一页...<br> <span style='color:blue;'>{$j}有效</span><br><span style='color:blue;'>".($i-$j)."无效</span>";
        exit("<script>window.location.href='".url("user/up","id=".$id."&page=".$p)."';</script>");
    }
    
    /**
     * 格式化数据
     * @param unknown $fields 字段['email'=>10,'phone'=>20]
     * @param unknown $val    数据 1442@qq.com,13312346678
     */
    public function check($fields, $val){
        $val = explode(',', $val);
        if(count($fields)!=count($val))
            return false; #无效
        
        #格式如['email','phone']
        $key  = array_keys($fields); 
        $d = [];
        foreach($key as $k => $v){
            #数据
            $vv = str_replace(array("\r\n", "\n", "\r"," "), "", $val[$k]);            
            $d[$v] = $vv;
        }
        return $d;
    }
    
    
    /**
     * 取得值
     * @param unknown $val
     */
    public function getData($d){
        #身份证
        if(isset($d['idcard'])){
            $rs = $this->getIdcard($d['idcard']);
            if($rs==false)
                return false;
            $d = array_merge($d,$rs);
        }
        #银行卡
        if(isset($d['bank_no'])){
            $rs = $this->getBank($d['bank_no']);
            if($rs==false)
                return false;
            $d = array_merge($d,$rs);
        }
        return $d;
    }
    
    //身份信息
    public function getIdcard($idcard){
        if(strlen($idcard) != 15 && strlen($idcard) != 18)
            return false;
        $code = substr($idcard,0,6);
        $d    = [
            'i_pro' => '',
            'i_city' => '',
            'i_county' => '',
            'i_sex' => 2,
            'i_age' => '',
            'i_birthday' => '',
        ];
        $temp = $this->idcard;
        #归属地
        if( isset($temp[$code]) ){
            $d['i_pro']   =$temp[$code]['pro'];
            $d['i_city']  =$temp[$code]['city'];
            $d['i_county']=$temp[$code]['county'];
        }
        #性别
        $d['i_sex'] = substr($idcard, (strlen($idcard)==15 ? -2 : -1), 1) % 2 ==0 ? 1 : 0;
        
        #生日
        $d['i_birthday'] = strtotime(strlen($idcard)==15 ? ('19' . substr($idcard, 6, 6)) : substr($idcard, 6, 8));
        
        #年龄
        $d['i_age'] = (int)date("Y") - (int)date("Y",$d['i_birthday']);
        return $d;   
    }
    
    //银行卡信息
    public function getBank($bank_no){
        $d    = [];
        $temp = $this->bank;
        $t = [
            'b_name' => '',
            'b_type' => 0,
            'b_title' => '',
        ];
        if(isset($temp[substr($bank_no, 0,8)])){
            $t = $temp[substr($bank_no, 0,8)];
        }elseif(isset($temp[substr($bank_no, 0,6)])){
            $t = $temp[substr($bank_no, 0,6)];            
        }elseif(isset($temp[substr($bank_no, 0,5)])){
            $t = $temp[substr($bank_no, 0,5)];            
        }elseif(isset($temp[substr($bank_no, 0,4)])){
            $t = $temp[substr($bank_no, 0,4)];
        }
        return [
            'b_name' => $t['b_name'],
            'b_type' => $t['b_type'],
            'b_title' => $t['b_title'],
        ];
    }
    
    
    //整理数据库
    public function formate($step=1){
        if($step==1){ #匹配电话号码
            $sql = "
            update
                user,data_phone
            set
                user.p_pro=data_phone.pro,
                user.p_city=data_phone.city,
                user.p_yy=data_phone.p_yy
            where
                left(user.phone,7)=data_phone.code and user.p_pro=''";
            Db::query($sql);
            $sql = "
            update
                user_history,data_phone
            set
                user_history.p_pro=data_phone.pro,
                user_history.p_city=data_phone.city,
                user_history.p_yy=data_phone.p_yy
            where
                left(user_history.phone,7)=data_phone.code and user_history.p_pro=''";
            Db::query($sql);
            echo "{$step}: 同步电话号码完成,等待跳转...<br>";
            exit("<script>window.location.href='".url("user/formate","step=2")."';</script>");
        }elseif($step==2){ #去重
            $sql = "
                delete 
                    from user   
                where
                    (phone,idcard) in (select phone,idcard from (select phone,idcard from user GROUP BY phone,idcard HAVING COUNT(*)>1) s1)   
                and  
                    id not in (select id from (select id from user GROUP BY phone,idcard HAVING COUNT(*)>1) s2); 
                ";
            Db::query($sql);
            echo "{$step}: 去除重复数据完成,等待跳转...<br>";
            exit("<script>window.location.href='".url("user/formate","step=3")."';</script>");
        }elseif($step==3){ #同一电话，多个来源
            $sql = "
            update
                user,(select phone,group_concat(froms) as phone_froms from user_history group by phone,froms) temp
            set
                user.phone_froms=temp.phone_froms
            where                
                user.phone=temp.phone";
            Db::query($sql);
            echo "{$step}: 同步同一电话的多个来源,等待跳转...<br>";
            exit("<script>window.location.href='".url("user/formate","step=4")."';</script>");
        }elseif($step==4){ #同一身份证，多个来源
            $sql = "
            update
                user,(select idcard,group_concat(froms) as idcard_froms from user_history group by idcard,froms) temp
            set
                user.idcard_froms=temp.idcard_froms
            where                
                user.idcard=temp.idcard";
            Db::query($sql);
            echo "{$step}: 同步同一身份证的多个来源,等待跳转...<br>";
            exit("<script>window.location.href='".url("user/formate","step=5")."';</script>");
        }elseif($step==5){ #同一电话，多个身份证
            $sql = "
            update
                user,(select phone,group_concat(idcard) as phone_idcards from user_history group by phone,idcard) temp
            set
                user.phone_idcards=temp.phone_idcards
            where                
                user.phone=temp.phone";
            Db::query($sql);
            echo "{$step}: 同步同一电话多个身份证,等待跳转...<br>";
            exit("<script>window.location.href='".url("user/formate","step=6")."';</script>");
        }elseif($step==6){ #同一身份证，多个电话
            $sql = "
            update
                user,(select idcard,group_concat(phone) as idcard_phones from user_history group by phone,idcard) temp
            set
                user.idcard_phones=temp.idcard_phones
            where                
                user.idcard=temp.idcard";
            Db::query($sql);
            echo "{$step}: 同步同一身份证多个电话,等待跳转...<br>";
            exit("<script>window.location.href='".url("user/formate","step=7")."';</script>");
        }elseif($step==7){
            exit("经过以上6步。 整理数据完成......(~~!)");
        }
        
    }
}